Corelated query:

    Here both the inner query & outer query runs parallel based on the condition specified is k.a., Corelated query.

Q: Why required?
Ans:  As we are aware that the sub query is slow & takes more time to execute. Bcoz in sub query the inner most query runs first & based on the output of the inner query the outer query will get execute. Hence this makes sub query slow.
  To overcome this we use corelated query. 

Note: Whatever we can achieve using sub query can be achieved using corelated query 
--------------------------

Corelated query works as follows:
Ex: select * from emp where 1=1;
As the condition is true, this query gives the output.

Ex: select * from emp where 1=2;
As the condition is false, this query won't execute.
-------------------------

Q: Find nth max salary from emp table?
Ans:
select distinct(a.sal) from emp a where &n=(select 
count(distinct(b.sal)) from emp b where a.sal<=b.sal);


Examples:
Q1: Find second max salary from emp?
Q2: Find third max salary from emp?
Q3: Find second min salary from emp?
Q4: Find third min salary from emp?
Q5: Identify the employee name who is getting max salary from emp table?
Q6: Identify the department names in which employees present?
Q7: Identify the department name in which emp are not present?
Q8: Identify the department name in which highest salaried emp present?
Q9: Among salesman who is getting max sal?
Q10: Find the location in which min salaried employee present?


